package com.ylfstu.jdbc.datasource;


import com.ylfstu.jdbc.JdbcUtils.JdbcUtils;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;

/**
 * @Author: Joy_yang
 * @Date: 2022/10/20 17:41
 */
public class JdbcUtilsByDruid_USE {
    @Test
    public void testSelect() {

        //使用druid完成
        //1. 得到连接
        Connection connection = null;
        //2. 执行SQL
        String sql = "select * from actor where id >= ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date date = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t" + phone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JdbcUtilsByDruid.close(resultSet, preparedStatement, connection);
        }
    }

    //使用土办法实现 resultSet =>封装到 Arraylist
    @Test
    public void testSelectToArrayList() {

        //使用druid完成
        //1. 得到连接
        Connection connection = null;
        //2. 执行SQL
        String sql = "select * from actor where id >= ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Actor> list = new ArrayList<>(); //创建ArrayList对象，存放Actor对象
        try {
            connection = JdbcUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date date = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                //把得到的resultSet 的记录封装到 Actor 对象，放入到list集合
                list.add(new Actor(id, name, sex, date, phone));
            }
            System.out.println("集合记录=" + list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JdbcUtilsByDruid.close(resultSet, preparedStatement, connection);
        }
    }
}
